IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventLog]') AND type in (N'U'))
BEGIN
     DROP TABLE dbo.EventLog
END
GO

CREATE TABLE dbo.EventLog(
     EventLogId        bigint IDENTITY(1,1) NOT NULL,
     EventLogTypeId    int NOT NULL,
     LogDt             datetime NOT NULL DEFAULT (getdate()),
     HostName          varchar(128) NOT NULL DEFAULT (host_name()),
     LoginName         varchar(128) NOT NULL DEFAULT (suser_sname()),
     DatabaseUserName  varchar(128) NULL DEFAULT (user_name()),
     DatabaseName      varchar(128) NULL DEFAULT (db_name()),
     AppName           varchar(128) NULL DEFAULT (app_name()),
     SPID              int NOT NULL DEFAULT (@@spid),
     [SID]             varbinary(85) NULL,
     LogByApp          varchar(128) NULL,
     LogByAppSubSystem varchar(128) NULL,
     LogByDesc         varchar(128) NULL,
     LogText           varchar(6144) NULL,
     TraceText         varchar(max) NULL,
     TraceImage        varbinary(max) NULL,
     MiscVC1           varchar(128) NULL,
     MiscVC2           varchar(128) NULL,
     MiscVC3           varchar(128) NULL,
     MiscVC4           varchar(128) NULL,
     MiscInt1          int NULL,
     MiscInt2          int NULL,
     MiscInt3          int NULL,
     MiscInt4          int NULL,
     MiscTinyint1      tinyint NULL,
     MiscTinyint2      tinyint NULL,
     MiscTinyint3      tinyint NULL,
     MiscTinyint4      tinyint NULL,
     MiscBigInt1       bigint NULL,
     MiscBigInt2       bigint NULL,
     MiscBigInt3       bigint NULL,
     MiscBigInt4       bigint NULL,
     MiscDt1           datetime NULL,
     MiscDt2           datetime NULL,
     MiscDt3           datetime NULL,
     MiscDt4           datetime NULL,
     MiscGUID1         uniqueidentifier NULL,
     MiscGUID2         uniqueidentifier NULL,
     MiscGUID3         uniqueidentifier NULL,
     MiscGUID4         uniqueidentifier NULL,
     CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED ( EventLogId ASC )
)
GO

CREATE NONCLUSTERED INDEX IX_EventLog_App_AppSubSystem_Desc ON dbo.EventLog 
     ( LogByApp ASC, LogByAppSubSystem ASC, LogByDesc ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_AppName ON dbo.EventLog ( AppName ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_DatabaseName ON dbo.EventLog ( DatabaseName ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_EventLogTypeId ON dbo.EventLog ( EventLogTypeId ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_HostName ON dbo.EventLog ( HostName ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_LogDt ON dbo.EventLog ( LogDt ASC )
GO

CREATE NONCLUSTERED INDEX IX_EventLog_LoginName ON dbo.EventLog ( LoginName ASC )
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Event Log entry.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'EventLogId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Identifier of a particular Event Log Type.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'EventLogTypeId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date/time when event was logged.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogDt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Host Name of logged activity.  Caution!  Is not set by system therefore can be spoofed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'HostName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Server login name of logged event.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LoginName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Username in the database of logged event.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'DatabaseUserName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database name where activity was logged into.  For single application use will usually match database of application, for server level use will usually match database of calling application.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'DatabaseName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Application Name of logged activity.  Caution!  Is not set by system therefore can be spoofed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'AppName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Server Process Id of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'SPID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SID of user of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'SID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogByApp'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application Sub-System.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogByAppSubSystem'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event Logged by Application Description, usually additional additional detail such as sub-sub-process, or internal identifier.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogByDesc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Log message of the logged activity.  6K Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'LogText'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Traced text of logged activity.  2GB Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'TraceText'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Traced binary image of logged activity.  2GB Max.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'TraceImage'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscVC1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscVC2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscVC3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc varchar(128) 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscVC4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscInt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscInt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscInt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc int 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscInt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc tinyint 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscTinyint4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc bigint 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscBigInt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscDt1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscDt2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscDt3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc datetime 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscDt4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 1 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 2 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 3 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Misc GUID 4 of logged activity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog', @level2type=N'COLUMN',@level2name=N'MiscGUID4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event logging.  Contains logged events.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EventLog'
GO
